package com.sql.common;


import com.lcx.util.ReflectUtil;
import com.sql.common.annotation.Id;
import com.sql.common.intc.ParseUtilConvert;
import com.sql.common.intc.SqlTypeConvert;
import com.sql.common.util.FieldSetter;
import com.sql.common.util.NameUtil;
import com.sql.common.util.ParseUtil;
import com.sql.common.util.ParseUtilConvertors;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Shisan on 2017/11/24.
 */
public class DaoImpl implements Dao{

    private Connection connection;

    public DaoImpl(Connection connection) {
        this.connection = connection;
    }



    @Override
    public List<?> get(Class<?> clazz) throws Exception {
        PreparedStatement pStatement = connection.prepareStatement("SELECT * FROM "+getTableName(clazz));
        List<?> list = selectQuery(pStatement,clazz);
        return list;
    }

    @Override
    public List<?> get(Class<?> clazz, String where, Object[]params) throws Exception {
        where = NameUtil.toDatabaseNamed(where);
        PreparedStatement pStatement = connection.prepareStatement("SELECT * FROM "+getTableName(clazz)+" where "+where);
        if(params != null){
            for(int i=0;i<params.length;i++){
                pStatement.setObject(i+1,params[i]);
            }
        }
        List<?> list = selectQuery(pStatement,clazz);
        return list;
    }

    @Override
    public <T> T getOne(Class<?> clazz, String where, Object[]params) throws Exception {
        List<?> list = get(clazz, where,params);
        if(list!=null && !list.isEmpty()){
            return (T) list.get(0);
        }
        return null;
    }

    @Override
    public <T> T getById(Class<?> clazz, Object id) throws Exception {
        Field f = getIdField(clazz);
        if(f == null) throw new Exception("对象未指定ID!");
        PreparedStatement pStatement = connection.prepareStatement("SELECT * FROM "+getTableName(clazz)+" where "+NameUtil.toDatabaseNamed(f.getName())+" = ?");
        pStatement.setObject(1,id);
        List<?> list = selectQuery(pStatement,clazz);
        if(list!= null && !list.isEmpty()){
            return (T) list.get(0);
        }
        return null;
    }

    @Override
    public List<?> getBySql(Class<?> clazz, String sql, Map<Integer,SqlTypeConvert> mapTypeConvert,Object... params) throws Exception {
        PreparedStatement pStatement = connection.prepareStatement(sql);
        if(params != null){
            for(int i=0;i<params.length;i++){
                pStatement.setObject(i+1,params[i]);
            }
        }
        return selectQuerySql(pStatement,clazz,mapTypeConvert);
    }

    @Override
    public int insert(Object obj) throws Exception {
        List<Field> fields = ReflectUtil.getFields(obj.getClass());
        List<Object> values = new ArrayList<>();
        StringBuilder sb = new StringBuilder("( ");
        StringBuilder sb2 = new StringBuilder(" VALUES(");
        boolean isFirst = true;
        Method autoKeyMethod = null;
        Class<?> autoKeyType=null;
        for(Field f:fields){
            try{
                Method m = ReflectUtil.getReadMethodByField(f);
                values.add(m.invoke(obj));
                if(!isFirst){
                    sb.append(",");
                    sb2.append(",");
                }
                sb.append(NameUtil.toDatabaseNamed(f.getName()));
                sb2.append("?");
                Id idA = f.getAnnotation(Id.class);
                if(idA!=null && idA.autoGenerator()){
                    autoKeyMethod = ReflectUtil.getWriteMethodByField(f);
                    autoKeyType=f.getType();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            isFirst=false;
        }
        sb.append(") ");
        sb2.append(") ");
        sb.append(sb2.toString());
        PreparedStatement pStatement = null;
        int index= 0;
        try {
            pStatement = connection.prepareStatement("INSERT INTO "+getTableName(obj.getClass())+sb.toString());
            for(int i=0;i<values.size();i++){
                pStatement.setObject(i+1,values.get(i));
            }
            index = pStatement.executeUpdate();
            if(autoKeyMethod!=null){
                ResultSet resultSet = pStatement.getGeneratedKeys();
                Object id=null;
                if(resultSet.next()){
                    id=resultSet.getObject(1);
                    autoKeyMethod.invoke(obj, ParseUtil.parse(id,autoKeyType,null));
                }
            }
        } catch (Exception e) {
            throw e;
        }finally {
            try {
                pStatement.close();
            } catch (Exception e) {
            }
        }
        return index;
    }

    @Override
    public int update(Object obj) throws Exception {
        List<Field> fields = ReflectUtil.getFields(obj.getClass());
        List<Object> values = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        boolean isFirst = true;
        Method autoKeyMethod = null;
        String keyName=null;
        for(Field f:fields){
            try{
                Method m = ReflectUtil.getReadMethodByField(f);
                Id idA = f.getAnnotation(Id.class);
                if(idA!=null){
                    autoKeyMethod = ReflectUtil.getReadMethodByField(f);
                    keyName=NameUtil.toDatabaseNamed(f.getName());
                    continue;
                }
                values.add(m.invoke(obj));
                if(!isFirst){
                    sb.append(",");
                }
                sb.append(NameUtil.toDatabaseNamed(f.getName())).append("=?");
            }catch (Exception e){
                e.printStackTrace();
            }
            isFirst=false;
        }
        sb.append(" WHERE ").append(keyName).append(" = ?");
        values.add(autoKeyMethod.invoke(obj));
        PreparedStatement pStatement = null;
        int index = 0;
        try {
            pStatement = connection.prepareStatement("UPDATE "+getTableName(obj.getClass())+" SET "+sb.toString());
            for(int i=0;i<values.size();i++){
                pStatement.setObject(i+1,values.get(i));
            }
            index = pStatement.executeUpdate();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pStatement.close();
            } catch (SQLException e) {
            }
        }
        return index;
    }

    @Override
    public int updateBySql(String sql, Object...params) throws Exception {
        int index = 0;
        PreparedStatement pStatement = null;
        try {
            pStatement = connection.prepareStatement(sql);
            if(params!=null){
                for(int i=0;i<params.length;i++){
                    pStatement.setObject(i+1,params[i]);
                }
            }
            index = pStatement.executeUpdate();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pStatement.close();
            } catch (SQLException e) {
            }
        }
        return index;
    }

    @Override
    public int delete(Class<?> clazz, Object id) throws Exception {
        Field f = getIdField(clazz);
        if(f == null) throw new Exception("对象未指定ID!");
        int index = 0;
        PreparedStatement pStatement = null;
        try {
            pStatement = connection.prepareStatement("DELETE FROM "+getTableName(clazz)+" where "+NameUtil.toDatabaseNamed(f.getName())+" = ?");
            pStatement.setObject(1,id);
            index = pStatement.executeUpdate();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pStatement.close();
            } catch (SQLException e) {
            }
        }
        return index;
    }

    @Override
    public int delete(Class<?> clazz, String where, Object... params) throws Exception {
        where = NameUtil.toDatabaseNamed(where);
        PreparedStatement pStatement = null;
        int index = 0;
        try {
            pStatement = connection.prepareStatement("DELETE FROM "+getTableName(clazz)+" where "+where);
            if(params != null){
                for(int i=0;i<params.length;i++){
                    pStatement.setObject(i+1,params[i]);
                }
            }
            index = pStatement.executeUpdate();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pStatement.close();
            } catch (SQLException e) {
            }
        }
        return index;
    }

    @Override
    public PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    private String getTableName(Class<?> clazz){
        String name = clazz.getSimpleName();
        name = name.substring(0,1).toLowerCase()+name.substring(1);
        return NameUtil.toDatabaseNamed(name);
    }

    private Field getIdField(Class<?> clazz){
        List<Field> list = ReflectUtil.getFields(clazz);
        for(Field f:list){
            if(f.getAnnotation(Id.class)!=null) return f;
        }
        return null;
    }

    private List<?> selectQuery(PreparedStatement pStatement, Class<?> clazz) throws Exception {
        ResultSet resultSet = null;
        List list = null;
        try {
            resultSet = pStatement.executeQuery();
            list = new ArrayList<>();
            Object val;
            while(resultSet.next()){
                List<Field> field = ReflectUtil.getFields(clazz);
                Object obj = clazz.newInstance();
                for(Field f:field){
                    try {
                        Method m = ReflectUtil.getWriteMethodByField(f);
                        val= resultSet.getObject(NameUtil.toDatabaseNamed(f.getName()));
                        val = ParseUtil.parse(val,f.getType(), ParseUtilConvertors.sqlTimeToDate);
                        m.invoke(obj,val);
                    } catch (Exception e) {
                        System.err.println("SELECT QUERY ERROR");
                    }
                }
                list.add(obj);
            }
        } catch (Exception e) {
            throw e;
        }finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            try {
                pStatement.close();
            } catch (SQLException e) {
            }
        }
        return list;
    }
    private List<?> selectQuerySql(PreparedStatement pStatement, Class<?> clazz, Map<Integer,SqlTypeConvert> mapResultConvert) throws Exception {
        ResultSet resultSet = null;
        List list = null;
        try {
            resultSet = pStatement.executeQuery();
            list = new ArrayList<>();
            Object val;
            while(resultSet.next()){
                if(clazz == Integer.class || clazz==int.class
                        || clazz == Long.class || clazz==long.class
                        || clazz == Short.class || clazz == short.class
                        || clazz == String.class || clazz == BigDecimal.class){
                    val = ParseUtil.parse(resultSet.getObject(1), clazz, new ParseUtilConvert() {
                        @Override
                        public Object convert(Class<?> targetType, Object value) {
                            if(value!=null && targetType==BigDecimal.class){
                                return new BigDecimal(value.toString());
                            }
                            return null;
                        }
                    });
                }else{
                    if(clazz.isAssignableFrom(Map.class) || clazz == Map.class){
                        Map map;
                        if(clazz == Map.class){
                            map = new LinkedHashMap();
                        }else{
                            map = (Map) clazz.getDeclaredConstructor().newInstance();
                        }
                        ResultSetMetaData data = resultSet.getMetaData();
                        int count = data.getColumnCount();
                        int index=0;
                        String tempCol;
                        Object value;
                        SqlTypeConvert convert;
                        for(int i=1;i<=count;i++){
                            String colName = data.getColumnName(i).toUpperCase();
                            tempCol = colName;
                            while(map.get(tempCol)!=null){
                                index++;
                                tempCol = colName+"("+index+")";
                            }
                            value = resultSet.getObject(i);
                            if(mapResultConvert!=null){
                                convert = mapResultConvert.get(data.getColumnType(i));
                                if(convert != null){
                                    value = convert.convert(value);
                                }
                            }
                            map.put(tempCol,value);
                            index=0;
                        }
                        val = map;
                    }else{
                        val = clazz.newInstance();
                        ResultSetMetaData data = resultSet.getMetaData();
                        int count = data.getColumnCount();
                        for(int i=1;i<=count;i++){
                            String colName = data.getColumnName(i);
                            try {
                                FieldSetter.setFieldByWriteMethod(clazz,val,NameUtil.toJavaNamed(colName),resultSet.getObject(colName));
                            } catch (Exception e) {
                            }
                        }
                    }
                }
                list.add(val);
            }
        } catch (Exception e) {
            throw e;
        }finally {
            try {
                resultSet.close();
            } catch (Exception e) {
            }
            try {
                pStatement.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
}
